package cn.joker.constant;


import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * created By huangxumin on
 * @author huangxumin
 */
public class ExcelUtil {

    public static List<List<Object>> getExcelTableInfo(String filePath) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filePath));
        HSSFSheet sheet = workbook.getSheetAt(0);
        List<List<Object>> result = getListsBySheet(sheet);
        return result;
    }

    public static List<List<Object>> getExcelTableInfo(File file) throws IOException {
        String fileName = file.getName();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            System.out.println("上传文件格式不正确，文件名称：" + fileName);
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }

        Workbook workbook = null;
        if (isExcel2003) {
            workbook = new HSSFWorkbook(new FileInputStream(file));
        } else {
            workbook = new XSSFWorkbook(new FileInputStream(file));
        }

        try {
            Sheet sheet = workbook.getSheetAt(0);
            return getListsBySheet(sheet);
        } finally {
            workbook.close();
        }
    }

    private static List<List<Object>> getListsBySheet(Sheet sheet) {
        List<List<Object>> result = new ArrayList<>();
        for (Row row : sheet) {
            List<Object> cells = new ArrayList<>();
            for (Cell cell : row) {
                //如果行数据是空字串 就不要了，跳过
                if ("".equals(cell.getStringCellValue())){
                    continue;
                }
                cells.add(getCellVal(cell));
            }
            result.add(cells);
        }
        return result;
    }
    /**
     *
     * @param cell
     * @return
     */
    private static Object getCellVal(Cell cell){
        Object obj = null;
        switch (cell.getCellType()) {
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case ERROR:
                obj = cell.getErrorCellValue();
                break;
            case NUMERIC:
                //这也是因为是interger才转的
                obj = new Double(cell.getNumericCellValue()).intValue();
                break;
            case STRING:
                obj = cell.getStringCellValue();
                break;
            default:
                break;
        }
        return obj;
    }


    /**
     * 从页面上抓取的Excel文件实际还是html格式，需要直接读取内容按照xml方式进行解析
     * @param file
     * @return
     */
    public static List<List<Object>> getExcelTableInfoByHtml(File file) throws IOException, DocumentException {
        String fileContent = FileUtil.getFileContent(file);
        // <meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8">
        fileContent = fileContent.replace("<meta http-equiv=\"content-type\" content=\"application/vnd.ms-excel; charset=UTF-8\">", "");

        Document document = DocumentHelper.parseText(fileContent);
        Element rootElement = document.getRootElement();

        List<List<Object>> result = new ArrayList<>();
        List<Element> elements = rootElement.element("body").element("table").elements("tr");
        for (Element element : elements) {
            List<Element> tds = element.elements("td");
            List<Object> cells = new ArrayList<>();
            for (Element td : tds) {
                String stringValue = td.getStringValue();
                cells.add(stringValue);
            }

            result.add(cells);
        }

        return result;
    }
}
